import web
class Schema:
    db = web.config.get('database')
    def users(self):
        sql  = '''
        CREATE TABLE IF NOT EXISTS `users` (
            id int(8) NOT NULL AUTO_INCREMENT,
            account varchar(50) NOT NULL,
            email varchar(100) NOT NULL,
            password varchar(64) NOT NULL,
            is_admin tinyint(1) DEFAULT '0',
            created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
            status TINYINT(1) DEFAULT '1',
            PRIMARY KEY (`id`),
            UNIQUE KEY `index_users_on_account` (`account`),
            UNIQUE KEY `index_users_on_email` (`email`)
        ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
        '''
        print self.db.query(sql)

    def sites(self):
        sql = '''
        CREATE TABLE IF NOT EXISTS `sites` (
            id int(8) NOT NULL AUTO_INCREMENT,
            fid int(8) DEFAULT '0',
            uid int(8) NOT NULL,
            level int(5) NOT NULL,
            name varchar(100),
            domain varchar(50) NOT NULL,
            host varchar(50),
            port char(10),
            is_cname tinyint(1) DEFAULT '0',
            information varchar(200),
            traffic_day float(10,2) DEFAULT '0.00',
            traffic_week float(10,2) DEFAULT '0.00',
            traffic_month float(10,2) DEFAULT '0.00',
            traffic_total float(10,2) DEFAULT '0.00',
            created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
            status tinyint(1) DEFAULT '1',
            PRIMARY KEY (`id`),
            UNIQUE KEY `index_sites_on_domain` (`domain`),
            KEY `index_sites_on_fid` (`fid`),
            KEY `index_sites_on_uid` (`uid`),
            KEY `index_sites_on_level` (`level`)
        ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
        '''
        print self.db.query(sql)
    def sites_node(self):
        sql ='''
        CREATE TABLE IF NOT EXISTS `sites_node` (
            id int(8) NOT NULL AUTO_INCREMENT,
            sid int(8) NOT NULL,
            nid int(8) NOT NULL,
            traffic_total float(10,2) DEFAULT '0.00',
            created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
            status tinyint(1) DEFAULT '1',
            PRIMARY KEY (`id`),
            KEY `index_sites_node_on_sid` (`sid`),
            KEY `index_sites_node_on_nid` (`nid`)
        ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
        '''
        print self.db.query(sql)
    def sites_traffic(self):
        sql = '''
        CREATE TABLE IF NOT EXISTS `sites_traffic` (
            id int(8) NOT NULL AUTO_INCREMENT,
            sid int(8) NOT NULL,
            hit_byte int(10) NOT NULL,
            hit_count int(10) NOT NULL,
            total_byte int(10) NOT NULL,
            total_count int(10) NOT NULL,
            created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
            status tinyint(1) DEFAULT '1',
            PRIMARY KEY (`id`),
            KEY `index_sites_traffic_on_sid` (`sid`)
        ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
        '''
        print self.db.query(sql)
    def levels(self):
        sql = '''
        CREATE TABLE IF NOT EXISTS `levels` (
            id int(5) NOT NULL AUTO_INCREMENT,
            uid int(8) NOT NULL,
            name varchar(100),
            type tinyint(1) NOT NULL,
            created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
            status tinyint(1) DEFAULT '1',
            PRIMARY KEY (`id`),
            KEY `index_levels_on_uid` (`uid`)
        ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
        '''
        print self.db.query(sql)

    def nodes(self):
        sql = '''
        CREATE TABLE IF NOT EXISTS `nodes` (
            id int(8) NOT NULL AUTO_INCREMENT,
            uid int(8) NOT NULL,
            level int(5) NOT NULL,
            name varchar(100),
            host varchar(50),
            token varchar(81),
            information varchar(200),
            created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
            status tinyint(1) DEFAULT '1',
            PRIMARY KEY (`id`),
            UNIQUE KEY `index_nodes_on_host` (`host`),
            KEY `index_nodes_on_level` (`level`)
        ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
        '''
        print self.db.query(sql)

    def quests(self):
        sql = '''
        CREATE TABLE IF NOT EXISTS `quests` (
            id int(8) NOT NULL AUTO_INCREMENT,
            sid int(8) NOT NULL,
            type tinyint(1),
            created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
            status tinyint(1) DEFAULT '1',
            PRIMARY KEY (`id`)
        ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
        '''
        print self.db.query(sql)

    def traffics(self):
        sql = '''
        CREATE TABLE IF NOT EXISTS `traffics` (
            id int(8) NOT NULL AUTO_INCREMENT,
            sid int(8) NOT NULL,
            nid int(8)  NOT NULL,
            hit_byte int(10) NOT NULL,
            hit_count int(10) NOT NULL,
            total_byte int(10) NOT NULL,
            total_count int(10) NOT NULL,
            created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
            status tinyint(1) DEFAULT '1',
            PRIMARY KEY (`id`),
            KEY `index_traffics_on_sid` (`sid`),
            KEY `index_traffics_on_nid` (`nid`)
        ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
        '''
        print self.db.query(sql)
